﻿Imports System.Data.SqlClient
Public Class FrmLapPhieuTraBanDoDiaChinh
    Public m_tennhap As String
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHuyen.DisplayMember = "T_huyen"
        ComboBoxHuyen.ValueMember = "maH"
        ComboBoxHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        ComboBoxLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        ComboBoxLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub TimKiemThongTinBanDo()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select " &
                                "   dc.MasoBD " &
                                "  ,dc.NamThanhLap " &
                                "  ,dc.ToBanDoSo " &
                                "  ,tl.TenTyLe " &
                                "  ,dc.DiaDanh " &
                                "  ,ke.tenke " &
                                "  ,k.TenKho " &
                                "  ,m.nguoimuon" &
                                " , m.donvicongtac " &
                                " , m.diachi " &
                                " , m.mucdichmuon " &
                                " , m.ngayMuon " &
                                " From  BanDoDiaChinh dc, DMTyLe tl,  DMTaiLieu tli, DMKho k, DMKe ke, tenxa tx, tenhuyen th, BanDoMuon m " &
                                " WHERE dc.tyleid = tl.matyle " &
                                " and dc.MaLoaiTaiLieuID = tli.maloaiTaiLieuID " &
                                " and dc.makhoid = k.makhoid " &
                                " and dc.maKeid = ke.makeid " &
                                " and dc.MaHuyenId = '" & ComboBoxHuyen.SelectedValue.ToString & "' " &
                                " and dc.mahuyenid = th.maH " &
                                " and dc.MaXaId = '" & ComboBoxXa.SelectedValue.ToString & "' " &
                                " and dc.maxaid = tx.maxa " &
                                " and dc.masoBD = m.masoBD " &
                                " and m.matinhtrangid = '2' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewTraBando.DataSource = tbl
        If (tbl.Rows.Count = 0) Then
            MessageBox.Show("Không tìm thấy thông tin !")
        Else
        End If
        ob_cnn.Close()
    End Sub
    Private Sub NapBanDoVaoTextBox()
        TextBoxMasoBD.DataBindings.Clear()
        TextBoxMasoBD.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "MaSoBD")
        TextBoxNamThanhLap.DataBindings.Clear()
        TextBoxNamThanhLap.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "NamThanhLap")
        TextBoxToBanDo.DataBindings.Clear()
        TextBoxToBanDo.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "tobandoso")
        TextBoxTyLe.DataBindings.Clear()
        TextBoxTyLe.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "TenTyle")
        TextBoxDiaDanh.DataBindings.Clear()
        TextBoxDiaDanh.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "Diadanh")
        TextBoxKe.DataBindings.Clear()
        TextBoxKe.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "TenKe")
        TextBoxKho.DataBindings.Clear()
        TextBoxKho.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "TenKho")
        TextBoxNguoiMuon.DataBindings.Clear()
        TextBoxNguoiMuon.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "NguoiMuon")
        TextBoxDonViCongTac.DataBindings.Clear()
        TextBoxDonViCongTac.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "donvicongtac")
        TextBoxDiaChi.DataBindings.Clear()
        TextBoxDiaChi.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "diachi")
        TextBoxMucDichMuon.DataBindings.Clear()
        TextBoxMucDichMuon.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "mucdichmuon")
        DateNgayMuon.DataBindings.Clear()
        DateNgayMuon.DataBindings.Add("Text", DataGridViewTraBando.DataSource, "ngaymuon")
      
    End Sub
    Private Sub FrmLapPhieuTraBanDoDiaChinh_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridViewTraBando.RowsDefaultCellStyle.BackColor = Color.LightBlue
        DataGridViewTraBando.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        NapDonViHuyen()
        NapLoaiTaiLieu()
    End Sub
    Private Sub CapNhatBanDo()
        Try
            Dim Sql As String = ""
            Sql = " Update BanDoDiaChinh Set  MatinhtrangID  = '1'  where Masobd = '" & TextBoxMasoBD.Text & "'"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            ' NaplaiTraBanDo()
            ' NapDataGridViewTraBanDo()

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
    Private Sub CapNhatBanDoMuon()
        Try
            Dim NgayTra As String
            NgayTra = String.Format("{0:MM/dd/yyyy}", DateNgayTra.Value)

            Dim Sql As String = ""
            Sql = " Update BanDoMuon Set  NgayTra  =  '" & NgayTra & "' , MaTinhTrangID ='1'  where MasoBD = '" & TextBoxMasoBD.Text & "'"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            '   NaplaiTraBanDo()
            'NapDataGridViewTraBanDo()

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub

    Private Sub ComboBoxHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxHuyen.SelectedIndexChanged
        ' load ten huyen
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & ComboBoxHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxXa.DisplayMember = "TenXa"
        ComboBoxXa.ValueMember = "maxa"
        ComboBoxXa.DataSource = tbl
        ob_cnn.Close()
    End Sub

    Private Sub ComboBoxXa_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxXa.SelectedIndexChanged
        'NapDataGridViewTraBanDo()
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Me.Close()

    End Sub

    Private Sub DataGridViewTraBando_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewTraBando.CellClick

    End Sub

    Private Sub DataGridViewTraBando_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewTraBando.CellContentClick
        NapBanDoVaoTextBox()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        TimKiemThongTinBanDo()
    End Sub

    Private Sub TextBoxDiaDanh_TextChanged(sender As Object, e As EventArgs) Handles TextBoxDiaDanh.TextChanged
        ' TimKiemThongTinDiaDanh()
    End Sub

    Private Sub TextBoxNguoiMuon_TextChanged(sender As Object, e As EventArgs) Handles TextBoxNguoiMuon.TextChanged
        '  TimKiemThongTinNguoiMuon()
    End Sub
    Private Sub LamTuoi()
        TextBoxDiaChi.Text = ""
        TextBoxDiaDanh.Text = ""
        TextBoxDonViCongTac.Text = ""
        TextBoxMasoBD.Text = ""
        TextBoxMucDichMuon.Text = ""
        TextBoxNamThanhLap.Text = ""
        TextBoxNguoiMuon.Text = ""
        TextBoxToBanDo.Text = ""
        TextBoxTyLe.Text = ""
        TextBoxKe.Text = ""
        TextBoxKho.Text = ""
    End Sub

    Private Sub ButtonCapNhat_Click(sender As Object, e As EventArgs) Handles ButtonCapNhat.Click
        If TextBoxToBanDo.Text = "" Then
            MessageBox.Show("Tờ bản đồ không được rỗng")
            Return
        End If
        If TextBoxNguoiMuon.Text = "" Then
            MessageBox.Show(" Thông tin người mượn không được rỗng")
            Return
        Else
            CapNhatBanDoMuon()
            CapNhatBanDo()
            LamTuoi()
            MessageBox.Show("Đã cập nhật xong thông tin ")
            TimKiemThongTinBanDo()

        End If

    End Sub
End Class